﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Components.Authorization;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using OfficeOpenXml;



namespace SubstrateHelper.Pages
{
    public class PageDownloadDbModel : PageModel
    {
        SubstrateHelper.Data.SubstrateHelperDbContext Db;
        
        public string Message { get; set; }
        public PageDownloadDbModel(SubstrateHelper.Data.SubstrateHelperDbContext db)
        {
            Db = db;
        }
        public async Task<IActionResult> OnGetAsync()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            if (User.IsInRole("数据库管理员"))
            {
                ExcelPackage xlPackage = new ExcelPackage();
                BuildExcelFile(xlPackage);
                byte[] bt = xlPackage.GetAsByteArray();
                FileContentResult result = new FileContentResult(bt, "application/octet-stream");
                result.FileDownloadName = $"数据框架-{DateTime.Now.ToString("yyyyMMdd")}.xlsx";
                return result;

            }
            else
            {
                Message = "您没有下载权限！您目前登录的账号没有包含“数据库管理员”角色！";
                return Page();
            }
        }

        

        void BuildExcelFile(ExcelPackage xlPackage)
        {

            MakeSheets(xlPackage);
            

            ////角色            
            //MakeSheet(xlPackage, "角色", Db.Roles);

            ////角色声明            
            //MakeSheet(xlPackage, "角色声明", Db.RoleClaims);

            ////用户            
            //MakeSheet(xlPackage, "用户", Db.Users);

            ////用户角色    
            //MakeSheet(xlPackage, "用户角色", Db.UserRoles);
            
            ////输入建议
            //MakeSheet(xlPackage, "输入建议", Db.InputSuggestings);

            ////工序            
            //MakeSheet(xlPackage, "工序", Db.Processes);

            ////物料            
            //MakeSheet(xlPackage, "物料", Db.ProductionEntities);

            ////库存            
            //MakeSheet(xlPackage, "库存", Db.Inventories);

            ////工序参数            
            //MakeSheet(xlPackage, "工序参数", Db.ProcessParameters);

            ////配方           
            //MakeSheet(xlPackage, "配方", Db.Ingredients);
        }


        

        //void MakeSheet<TEntity>(ExcelPackage xlPackage, string sheetName, DbSet<TEntity> dbSet)
        //    where TEntity : class
        //{
        //    var sheet = xlPackage.Workbook.Worksheets.Add(sheetName);
        //    string[] properties = null;

            

        //    int row = 2;
        //    foreach(var item in dbSet)
        //    {     
        //        //表头
        //        if (properties==null)
        //        {

        //            Microsoft.EntityFrameworkCore.Metadata.IEntityType entityType = Db.Model.FindEntityType(item.GetType());

        //            var props = entityType.GetProperties();//这个不会返回导航属性
        //            var length = props.Count();
        //            properties = new string[length+1];//后面加一个“操作”列
        //            for(int i = 0; i < length; i++)
        //            {
        //                properties[i] = props.ElementAt(i).Name;
        //                sheet.Cells[1, i + 1].Value = properties[i];//表头
        //            }
        //            sheet.Cells[1, length + 1].Value = "操作";//后面加一个“操作”列

        //            //var propInfos = item.GetType().GetProperties();
        //            //var length = propInfos.Length;
        //            //properties = new string[length];

        //            //for (int i = 0; i < length; i++)
        //            //{
        //            //    properties[i] = propInfos[i].Name;
        //            //    sheet.Cells[1, i + 1].Value = properties[i];//表头
        //            //}
        //        }

        //        //数据
        //        for (int i = 0; i < properties.Length-1; i++)
        //        {
        //            var value = item.GetType().GetProperty(properties[i]).GetValue(item);
        //            sheet.Cells[row, i + 1].Value = value;
        //        }
        //        sheet.Cells[row, properties.Length].Value = "不变";
        //        row++;
        //    }
        //}

        void MakeSheets(ExcelPackage xlPackage,bool includeRecords=false)
        {
            var contextProperties = Db.GetType().GetProperties();
            foreach(var prop in contextProperties)
            {
                var t = prop.PropertyType;
                if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(DbSet<>))
                {
                    //表名
                    var args = t.GetGenericArguments();
                    if (args == null || args.Length < 1)
                    {
                        continue;
                    }
                    string sheetName =prop.Name;

                    //是否包含记录表
                    if (!includeRecords)
                    {
                        if (sheetName.EndsWith("Records") || sheetName == "Schedules")
                        {
                            continue;
                        }
                    }

                    
                    
                    //
                    var dbset = prop.GetValue(Db);

                    var obj = dbset as IEnumerable<object>;
                    MakeSheet(xlPackage, sheetName, obj,args[0]);
                }
            }
        }

        void MakeSheet(ExcelPackage xlPackage, string sheetName, IEnumerable<object> dbSet,Type itemType)
        {
            //注意：Excel表的横竖坐标是从1开始

            //
            var sheet = xlPackage.Workbook.Worksheets.Add(sheetName);
            string[] properties = null;


            //表头
            Microsoft.EntityFrameworkCore.Metadata.IEntityType entityType = Db.Model.FindEntityType(itemType);
            var props = entityType.GetProperties();//这个不会返回导航属性
            var length = props.Count();
            properties = new string[length + 1];//后面加一个“操作”列
            for (int i = 0; i < length; i++)
            {
                properties[i] = props.ElementAt(i).Name;
                sheet.Cells[1, i + 1].Value = properties[i];//表头
            }
            sheet.Cells[1, length + 1].Value = "操作";//后面加一个“操作”列


            //数据
            int row = 2;            
            foreach (var item in dbSet)
            {

                
                for (int i = 0; i < properties.Length - 1; i++)
                {
                    var value = item.GetType().GetProperty(properties[i]).GetValue(item);
                    sheet.Cells[row, i + 1].Value = value;
                }
                //sheet.Cells[row, properties.Length].Value = "不变";// => 空即表示不操作
                row++;
            }
        }
    }
}